<?php

class mlookupv1 extends CI_Model
{

    ########################################################################
    ####### DATA HOLIDAY ##############
    ########################################################################
    function sp_lookup_cud($proses = "", $id = "", $key = "")
    {
        $dblokal = $this->load->database("default", true);
        //$lookup_key = $key;
        $lookup_value = $this->input->post("lookup_value");
        $userid = $this->session->userdata('username');
        
        //spesial untuk outlet class amount
        $territory_id = $this->input->post("territory_id");
        $param_desc = $this->input->post("param_desc");
        $param_value = $this->input->post("param_value");
        $start_period = $this->input->post("start_period");
        $end_period = $this->input->post("end_period");

        $data = array("lookup_key" => $key,
                        "lookup_value" => $lookup_value,
                        "lookup_desc" => $lookup_value,
                        "created_by" => $this->session->userdata('username'), 
                        "updated_by" => $this->session->userdata('username'),
                        "created_on" => date("Y-m-d H:i:s"));
                        
        $data2 = array( "param_name" => $key,
                        "territory_id" => $territory_id,
                        "param_desc" => $param_desc,
                        "param_value" => $param_value,
                        "start_period" => $start_period, 
                        "end_period" => $end_period,
                        "created_by" => $this->session->userdata('username'), 
                        "updated_by" => $this->session->userdata('username'),
                        "created_on" => date("Y-m-d H:i:s"));

        if ($id <> "") {
            if($proses == "delete")
            {
                if($key == "outlet_class_amount")
                {
                    $sql = "update t_mtr_custom_param set param_name = '$key#$id', updated_by = '$userid' 
                        where param_id = $id";   
                    $dblokal->query($sql);    
                }
                else
                {
                    $sql = "update t_mtr_lookup set lookup_key = '$key#$id', updated_by = '$userid' 
                        where lookup_id = $id";   
                    $dblokal->query($sql);    
                }
                        
            }
            else
            {
                if($key == "outlet_class_amount")
                {
                    $dblokal->where(array("param_id" => $id));
                    $dblokal->update("t_mtr_custom_param", $data2);    
                }
                else
                {
                    $dblokal->where(array("lookup_id" => $id));
                    $dblokal->update("t_mtr_lookup", $data);    
                }
                 
            }
            
        } else {
            if($key == "outlet_class_amount")
            {
                $dblokal->insert("t_mtr_custom_param", $data2);    
            }
            else
            {
                $dblokal->insert("t_mtr_lookup", $data);    
            }
        }
    }
    
    function getTerr($username)
    {
        $dblokal = $this->load->database("default", true);
        $sql = "select f_get_cluster('$username')";   
        $query = $dblokal->query($sql);
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return array();
        }    
    }
    
    function getRegion()
    {
        $dblokal = $this->load->database("default", true);
        $sql = "select territory_id as mvalue, upper(territory_name) as mdisplay
                from t_mtr_territory
                where parent_id = 1 and territory_type_id = 2
                ORDER BY territory_name asc;";  
                 
        $query = $dblokal->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
    }
    
    function getCluster($region_id)
    {
        $dblokal = $this->load->database("default", true);
        $sql = "select territory_id as mvalue, CONCAT_WS(' ',split_part(upper(territory_name), ' ',1), CAST(split_part(territory_name, ' ',2) AS INTEGER)) AS mdisplay
                from t_mtr_territory
                where parent_id = $region_id and territory_type_id = 3
                order by split_part(territory_name, ' ',1), CAST(split_part(territory_name, ' ',2) AS INTEGER)";  
                 
        $query = $dblokal->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
    }
    
    function getTerrName($clusterId)
    {
        if($clusterId == 0)
        {
            return "";    
        }
        else
        {
            $dblokal = $this->load->database("default", true);
            $sql = "select f_get_territory_path_v2($clusterId)";   
            $query = $dblokal->query($sql);
            if ($query->num_rows() > 0) {
                return $query->row();
            } else {
                return array();
            }    
        }
            
    }

    function getKey($key = "")
    {
        $dblokal = $this->load->database("default", true);
        if ($key <> "") {
            $dblokal->where(array("lookup_key" => $key));
        }
        $query = $dblokal->get("t_mtr_lookup");
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
        
    }
    
    function getCustomParam($key = "", $username = "")
    {
        $dblokal = $this->load->database("default", true);
        
        $sql = "select f_get_cluster('$username')";   
        $queryf = $dblokal->query($sql)->row();
        
        
        if ($key <> "") {
            
            //$dblokal->where(array("param_name" => $key, "territory_id" => $queryf->f_get_cluster));
            
            
            //untuk kebutuhan formatting, casting param_value
            $dblokal->select('param_id, param_desc, param_value, start_period, end_period');
            $dblokal->from('t_mtr_custom_param');
            $dblokal->where('param_name', $key);
            $dblokal->where('territory_id', $queryf->f_get_cluster);
            //$query = $this->db->get();
        }
        //$query = $dblokal->get("t_mtr_custom_param");
        $query = $dblokal->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
        
    }

    function saveWeeklyHoliday($jenis, $hari, $nilai)
    {
        $dblokal = $this->load->database("default", true);
        $dblokal->where('type', $jenis);
        $dblokal->update('t_mtr_weekly_holiday', array('d' . $hari => $nilai));

        $this->updateWorkingDay("ds", date("m"));
    }


    #########################
    #########################

    function getCombo($sfa)
    {
        $dblokal = $this->load->database("default", true);
        $query = '';
        if($sfa == 'max')
        {
            $query = $dblokal->get("v_combo_oc_jc_sfa_max");    
        }
        else
        {
            $query = $dblokal->get("v_combo_oc_jc_sfa_min");   
        }
        
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }    
    }

    public function getById($id, $key = "")
    {
        #$id = intval( $id );
        $query = null;
        if($key == "outlet_class_amount")
        {
            $query = $this->db->where('param_id', $id)->get('t_mtr_custom_param');    
        }
        else
        {
            $query = $this->db->where('lookup_id', $id)->get('t_mtr_lookup');    
        }
        

        if ($query->num_rows() > 0) 
        {
            return $query->row();
        }
        else 
        {
            return array();
        }
    }

    public function getAll()
    {
        //get all records from users table
        $query = $this->db->get('t_mtr_holiday');

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
        }
    } //end getAll

    public function update()
    {
        $data = array('tanggal' => $this->input->post('tanggal', true), 'keterangan' =>
            $this->input->post('keterangan', true));
        $this->db->update('t_mtr_holiday', $data, array('id' => $this->input->post('id', true)));
    }

    public function delete($id)
    {
        /*
        * Any non-digit character will be excluded after passing $id
        * from intval function. This is done for security reason.
        */
        $id = intval($id);
        $this->db->delete('t_mtr_holiday', array('id' => $id));
    } //end delete
    
    function sp_kunjungan_jc_outlet_insupd($kelasOutlet,$lookup_value_min,$lookup_value_max,$userid)
    {
        $dbconn  = $this->load->database("default", TRUE);
        $sql = "SELECT sp_kunjungan_jc_outlet('$kelasOutlet', $lookup_value_min, $lookup_value_max, '$userid', '$userid')";  
        $query = $dbconn->query($sql);
        if( $query->num_rows() > 0) 
		{
            return $query->result();
        } 
		else {
            return array();
        }  
    }

    //added by alfin akhret
    function cek_jabatan_user($user_id)
    {
        $this->db->select('user_group_id');
        $query = $this->db->get_where('t_mtr_user', array('user_id' => $user_id));
        return $query->result();
    }

    function get_program_type()
    {
        $query = $this->db->get('t_mtr_program_type');
        return $query->result();
    }

    function get_current_territory($user_id)
    {
        $dbconn  = $this->load->database("default", TRUE);
        $sql = "select * from f_get_cluster('$user_id')";
        $query = $dbconn->query($sql);
        return $query->result();
    }

    function insert_new_program($caption = '', $desc = '', $program_type_id = '', $territory_id = '', $created_by)
    {
        $this->db->insert('t_mtr_program', array('caption'=>$caption, 'description'=>$desc, 'program_type_id'=>$program_type_id, 'territory_id'=>$territory_id, 'created_by'=>$created_by));
    }

    //added by: alfin akhret
    function getProgram($key = "")
    {
        //cek apakah data yg akan ditampilkan adalah program nasional atau program cluster
        $group_id = $this->cek_jabatan_user($this->session->userdata('username'));
        $job_title = (($group_id[0]->user_group_id == '10') || ($group_id[0]->user_group_id == '4')) ? $group_id[0]->user_group_id : false;

        if($job_title == '10')
        {
            $this->db->select('program_id, caption, istatus');
            $this->db->order_by('program_id', 'asc');
            $query = $this->db->get_where('t_mtr_program', array('territory_id'=> '1'));
        }
        else if($job_title == '4')
        {
            $cluster = $this->get_current_territory($this->session->userdata('username'));
            foreach ($cluster as $r) {
                $cluster = $r->f_get_cluster;
            }

            $this->db->select('program_id, caption, istatus');
            $this->db->order_by('program_id', 'asc');
            $query = $this->db->get_where('t_mtr_program', array('territory_id'=> $cluster));
        }
        else
        {
            $this->db->select('program_id, caption, istatus');
            $this->db->order_by('program_id', 'asc');
            $query = $this->db->get('t_mtr_program');
        }
        
        // var_dump($query->result());die();
        return $query->result();
    }

    function onOffProgram($program_id, $istatus)
    {
        $this->db->where('program_id', $program_id);
        $this->db->update('t_mtr_program', array('istatus' => $istatus));
    }

} //end class
